package com.psu.bean;

import java.sql.Connection;    
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.psu.connection.ConnectionJDBC;
import com.psu.accbean.AccMemberBean;
import com.psu.utils.Tools;

/**
 * Create By : Anusorn
 * Date : 15/11/13
 * Edit By : Anusorn
 * Date : 9/12/13
 */

public class MemberBean {
	AccMemberBean accMemberBean;
	public MemberBean(AccMemberBean accMemberBean){
		this.accMemberBean = accMemberBean;
	}
	
    /**
     *
     * @param commit
     * @return
     * @throws SQLException
     * @throws Exception
     */
	 public int insert(boolean commit) throws SQLException, Exception {
	        Connection conn = (Connection) ConnectionJDBC.getJDBConnection();
	        Statement stmt = null;
	        int rowsInsert = 0;
	        if (this.accMemberBean != null) {
	            String sql = "INSERT INTO SCCSSDB.user_detail"
	                    + "(Usr_Id, Usr_password, Usr_grp, Usr_fname, Usr_lname) "
	                    + "VALUES ("+Tools.replaceBlankDBTypeString(accMemberBean.getUsr_id())
	                    +", "+Tools.replaceBlankDBTypeString(accMemberBean.getUsr_password())
	                    +", "+Tools.replaceBlankDBTypeString(accMemberBean.getUsr_grp())
	                    +", "+Tools.replaceBlankDBTypeString(accMemberBean.getUsr_fname())
	                    +", "+Tools.replaceBlankDBTypeString(accMemberBean.getUsr_lname())
	                    +");";
	            System.out.println(sql);
	            try {
	                stmt = (Statement) conn.createStatement();
	                rowsInsert = stmt.executeUpdate(sql);
	                stmt.close();
	                if(commit){
	                	conn.commit();
	                }
	            } catch (SQLException e) {
	                stmt.close();
	                if(commit){
	                	conn.rollback();
	                }
	                //Logger.getLogger(MemberBean.class.getName()).log(Level.SEVERE, null, e);
	            }
	        }
	        if (conn != null) {
	             conn.close();
	        }
	        return rowsInsert;
	    }
	
    public ArrayList <Map<String, String>> selectQuery(String sql) throws SQLException{
	   System.out.println(">>>>> in MemberBean <<<<<");
	   System.out.println(">>>>> in mode selectQuery <<<<<");
	   System.out.println("-------------------------------------");
       Connection conn = (Connection) ConnectionJDBC.getJDBConnection();
       Statement stmt = null;
       ArrayList <Map<String, String>> list = new ArrayList<Map<String, String>>();
       ResultSet rs = null;
       ResultSetMetaData rsmd = null;
       System.out.println("SQL >>> "+sql);
       try{
           stmt = (Statement) conn.createStatement();
           rs = stmt.executeQuery(sql);
           rsmd = (ResultSetMetaData) rs.getMetaData();
           int colCount = rsmd.getColumnCount();
           String[] colNames = new String[colCount];
           String toCap = null;
           for (int j = 1; j <= colCount; j++){
               toCap = rsmd.getColumnName(j);
               colNames[j-1] = toCap.toUpperCase();
           }
           int rowNum = 0;
           while(rs.next()){
               //Represent Row Object
               Map<String, String> map = new HashMap<String, String>();
               for(int i = 1;i<= colCount;i++){
                   Object obj = rs.getObject(i);
                   if(obj != null){
                       map.put(colNames[i-1],obj.toString());
                   }else{
                       map.put(colNames[i-1],"");
                   }
               }
               list.add(rowNum,map);
               //Represent Table Object
               rowNum++;
           }//End Records
           
       }catch(Exception e){
           Logger.getLogger(MemberBean.class.getName()).log(Level.SEVERE, null, e);
       }finally{
           try{
               if (rs != null) rs.close();
               if (stmt != null) stmt.close();
               if (conn != null) conn.close();
           }catch(Exception e){
                Logger.getLogger(MemberBean.class.getName()).log(Level.SEVERE, null, e);
           }
       }
       return list;
   }
   
   public void updateQuery(String sql) throws SQLException {
       
	   Connection con = null; // connection reference variable for getting
       // connection
       Statement stmt = null; // Statement reference variable for query
       // Execution
       String conUrl = "jdbc:mysql://localhost:3306/";
       String driverName = "com.mysql.jdbc.Driver";
       String databaseName = "sccssdb";
       String usrName = "root";
       String usrPass = "root";
       System.out.println("SQL >>> "+sql);
       try {
               // Loading Driver
               Class.forName(driverName);
       } catch (ClassNotFoundException e) {
               System.out.println(e.toString());
       }
       try {
               // Getting Connection
               con = DriverManager.getConnection(conUrl + databaseName, usrName, usrPass);
               if (con != null) {
	                System.out.println("Connection is connected ");
	            } else {
	                System.out.println("Connection is not null ");
	            }
               System.out.println(sql);
               
               // Creating Statement for query execution
               stmt = con.createStatement();
               // Updating Table
               int rows = stmt.executeUpdate(sql);
               System.out.println(rows + " Rows Updated Successfully");
       } catch (Exception e) {
               System.out.println(e.toString());
       } finally {
               // Closing Connection
               con.close();
               stmt.close();
       }
       }
}
